package data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Properties;
import org.apache.commons.codec.binary.Base64;

import queries.SavedCommentsQueries;
import queries.UserQueries;
import values.Bank;
import values.Comment;
import values.RelatedComment;
import values.Topic;
import values.User;
import exception.ServerDownException;

/**
 * Connects to the database to authenticate and register users.
 */
public class DBManager 
{
	// <! - Constants - >
	/**
	 * Properties key, MYSQL driver classes.
	 */
	private final static String DRIVER = "db_driver";
	
	/**
	 * Properties key, MYSQL server's URI address.
	 */
	private final static String SERVER = "db_uri";
	
	/**
	 * Properties key, userName associated with DB account.
	 */
	private final static String USER = "db_u";
	
	/***
	 * Properties key, password associated with DB account.
	 */
	private final static String PASS = "db_p";
	
	/**
	 * Properties identifier for user
	 */
	private final static String MYSQL_USER="?user=";
	
	/**
	 * Properties identifier for password;
	 */
	private final static String MYSQL_PASSWORD="&password=";
	
	private final static String MYSQL_AUTOCOMMIT="&relaxAutoCommit=true";
	
	/**
	 * Apostrophe for executing SQL queries.
	 */
	private final static String APOSTROPHE="'";
	
	
	/**
	 * Comment type. The value 1 means the comment is a parent comment.
	 */
	private final static int DEFAULT_TYPE=1;
	
	
	/**
	 * Default value for the field helpful. 
	 */
	private final static int DEFAULT_HELPFUL=0;
	
	/**
	 * Default value for the field inappropiate.
	 */
	private final static String DEFAULT_INAPPROPIATE="";
	
	/**
	 * Default value for the field heading.
	 */
	private final static String DEFAULT_HEADING="";

	/**
	 * Sql sentence that gets the topics related to a comment.
	 */
	private final static String SELECT_TOPIC_GIVEN_COMMENT_ID="SELECT t.TOPIC FROM comment_topic c INNER JOIN topic t ON c.TOPIC_ID=t.TOPIC_ID WHERE c.COMMENT_ID=?";
	
	/**
	 * Sql sentence that gets the related comments given a comment id.
	 */
	private final static String SELECT_RELATED_COMMENTS_GIVEN_COMMENT_ID="SELECT * FROM comments INNER JOIN (SELECT RELATED_COMMENT_ID AS R FROM related_comments WHERE PARENT_ID =? ) AS G ON G.R = COMMENT_ID";
	
	/**
	 * Sql sentence that gets the bank name given a bank id.
	 */
	private final static String SELECT_BANK_NAME_GIVEN_BANK_ID="SELECT NAME FROM banks WHERE BANK_ID=?";
	
	/**
	 * Sql sentence that determines if a user has marked a given comment as helpful.
	 */
	private final static String SELECT_HELPFUL_COMMENTS_USER="SELECT count(*) as CONTADOR FROM helpful WHERE COMMENT_ID=? AND LOGIN =?";
	
	/**
	 * Sql sentence that return the id of the user that has marked a comment as inappropriate. 
	 */
	private final static String SELECT_INAPPROPIATE_COMMENTS_USER="SELECT INAPPROPIATE FROM comments WHERE COMMENT_ID =?";
	
	/**
	 * Sql sentence that return the comment topics;
	 */
	private final static String SELECT_GIVEN_COMMENT_TOPIC="SELECT TOPIC_ID FROM comment_topic WHERE COMMENT_ID =?";
	
	/**
	 * Sql sentence that retrieves the last comment id.
	 */
	private final static String SELECT_LAST_COMMENT_ID="SELECT  COMMENT_ID FROM  comments ORDER BY  COMMENT_ID DESC LIMIT 1";
	
	/**
	 * Sql sentence that retrieves the 5 most recent comments. 
	 */
	private final static String SELECT_5_RECENT_COMMENTS="SELECT * FROM comments WHERE TYPE =1 ORDER BY DATE, TIME LIMIT 5";
	
	/**
	 * Sql sentence that starts a multiple-update transaction. 
	 */
	private final static String START_TRANSACTION="START TRANSACTION";
	

	
	/**
	 * Sql sentence that updates a user comment counter.
	 */
	private final static String UPDATE_USER_COMMENT_COUNTER="UPDATE users set COUNTER=COUNTER+1 WHERE LOGIN=?";
	
	/**
	 * Sql sentence that updates a comment helpful counter.
	 */
	private final static String UPDATE_COMMENT_HELPFUL_COUNTER="UPDATE comments set HELPFUL=HELPFUL+1 WHERE COMMENT_ID=?";
	
	/**
	 * Sql sentence that updates the table helpful.
	 */
	private final static String UPDATE_TABLE_HELPFUL="INSERT INTO helpful (LOGIN, COMMENT_ID) VALUES (?,?)";
	
	/**
	 * Sql sentence that gets all the information from a comment given its id.
	 */
	private final static String SELECT_COMMENT_GIVEN_COMMENT_ID="SELECT * FROM comments WHERE COMMENT_ID=?";
	
	/**
	 * Sql sentence that gets the information of all banks.
	 */
	private final static String SELECT_BANKS="SELECT COUNT(BANK_ID)AS C , N, ID,L FROM comments INNER JOIN (SELECT NAME AS N,BANK_ID AS ID,LOGO AS L FROM banks) AS G ON G.ID = BANK_ID GROUP BY N ORDER BY C DESC";
	
	/**
	 * Sql sentence that gets the information of all topics. 
	 */
	private final static String SELECT_TOPICS="SELECT COUNT( TOPIC_ID )as COUNTER , TOPIC_ID , NAME FROM comment_topic INNER JOIN (SELECT TOPIC_ID AS ID, TOPIC AS NAME FROM topic) AS G ON G.ID = TOPIC_ID GROUP BY NAME ORDER BY COUNTER DESC";
	
	/**
	 * Secret.
	 */
	private final static byte[] KEY={84,87,53,122,89,121,120,52,101,122,85,47,98,109,116,104,85,83,119,43,81,81,61,61};
	
	
	// <! - Attributes - >
	/**
	 * Represents the session with the database.
	 */
	private Connection session;
	
	/**
	 * Properties file.
	 */
	private Properties config;
	
	/**
	 * Represent the class that contains the savedComments queries.
	 */
	private static SavedCommentsQueries savedComments;
	
	/**
	 * Represent the class that contains the userQueries class.
	 */
	private static UserQueries userQueries;
	
	// <! - Constructor - >
	public DBManager(Properties pConfig)
	{
		config = pConfig;
	}
	
	public static UserQueries userQueries()
	{
		if(userQueries==null)
			userQueries= new UserQueries();
		return userQueries;
			
	}
	
	/**
	 * Method that creates the class SavedCommentsQueries
	 */
	public static SavedCommentsQueries savedComments()
	{
		if(savedComments==null)
			savedComments=new SavedCommentsQueries();
		return savedComments;
	}
	
	// <! - Methods - >
	/**
	 * Connects to the data base.
	 */
	private void connect() throws ServerDownException
	{
		try
		{
			Class.forName(config.getProperty(DRIVER));

			session = DriverManager.getConnection(config.getProperty(SERVER)+MYSQL_USER+config.getProperty(USER)+MYSQL_PASSWORD+config.getProperty(PASS)+MYSQL_AUTOCOMMIT);

		}
		catch(ClassNotFoundException cne)
		{
			throw new ServerDownException(cne.getLocalizedMessage());
		}
		catch(SQLException sqle)
		{
			throw new ServerDownException(sqle.getLocalizedMessage());
		}
	}
	
	/**
	 * Method that disconnects the core layer from the database.
	 * @throws ServerDownException
	 */
	private void disconnect() throws ServerDownException
	{
		try
		{
			session.close();
		}
		catch(SQLException e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}

	
	/**
	 * Method that signs up the user.
	 * @param login
	 * @param password
	 * @param firstName
	 * @param lastName
	 * @param photo
	 * @throws ServerDownException 
	 */
	public void SignUpUser(String login, String password, String alias, String firstName, String lastName, String photo) throws ServerDownException
	{
		try
		{
			connect();
			
			PreparedStatement ps=session.prepareStatement("INSERT INTO users(LOGIN, PASSWORD, ALIAS, FIRST_NAME, LAST_NAME, PHOTO, RANKING, POINTS) VALUES (?,?,?,?,?,?,?,?)");
			
			ps.setString(1, login);
			
			String encriptedPassword=Security.encrypt(password, Base64.decodeBase64(KEY));
			ps.setString(2, encriptedPassword);
			
			ps.setString(3, alias);
			
			ps.setString(4, firstName);
			
			ps.setString(5, lastName);
			
			ps.setString(6, photo);
			
			ps.setInt(7, 1);
			
			ps.setInt(8,0);
			
			ps.executeUpdate();
			
			disconnect();
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	/**
	 * Method that gets the available banks from the database.
	 * @throws ServerDownException
	 */
	public ArrayList<Bank> getBanks() throws ServerDownException
	{	
		try
		{
			connect();
			
			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery(SELECT_BANKS);
			
			ArrayList<Bank> banks=new ArrayList<Bank>();

			Bank bank=null;
			
			
			while(rs.next())
			{
				int id=rs.getInt("ID");

				String name= rs.getString("N");
				
				//String logo=rs.getString("L");
				
				int counter=rs.getInt("C");
				
				 bank=new Bank(id, name, null,counter);

				
				banks.add(bank);
				

				
			}
			
			disconnect();
			
			
			return banks;
			
			
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	
	/**
	 * Method that gets the available topics from the database.
	 * @throws ServerDownException
	 */
	public ArrayList<Topic> getTopics() throws ServerDownException
	{

		
		try
		{
			connect();

			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery(SELECT_TOPICS);

			
			ArrayList<Topic> topics= new ArrayList<Topic>();

			
			while(rs.next())
			{
				int id=rs.getInt("TOPIC_ID");
				
				int counter=rs.getInt("COUNTER");
				
				String topicName=rs.getString("NAME");
				
				Topic topic= new Topic(id, topicName,counter);
				
				
				topics.add(topic);
				
				
			
			}
			
			disconnect();
			
			return topics;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
		
		
	}
	
	/**
	 * Method that inserts a related comment. 
	 */
	public void insertRelatedComment(int parentCommentId, String login, String content, Date date, Time time) throws ServerDownException
	{
		try
		{
			connect();
			PreparedStatement p=session.prepareStatement(START_TRANSACTION);
			p.execute();
			
			PreparedStatement ps=session.prepareStatement("INSERT INTO comments( DATE, TIME, COMMENT, LOGIN, BANK_ID,TYPE,HEADING,HELPFUL, INAPPROPIATE) VALUES (?,?,?,?,?,?,?,?,?)");
			
			ps.setDate(1,date);
			
			ps.setTime(2, time);
			
			ps.setString(3, content);
			
			ps.setString(4,login);
			
			//El bankId debe llegar como parametro
			ps.setInt(5,2);
			
			ps.setInt(6, DEFAULT_TYPE-1);
			
			ps.setString(7, DEFAULT_HEADING);
			
			ps.setInt(8, DEFAULT_HELPFUL);
			
			ps.setString(9, DEFAULT_INAPPROPIATE);
			
			ps.executeUpdate();
			
			
			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery(SELECT_LAST_COMMENT_ID);

			rs.next();
			
			int lastId=rs.getInt("COMMENT_ID");
			
			PreparedStatement ps1=session.prepareStatement("INSERT INTO  related_comments (RELATED_COMMENT_ID ,PARENT_ID)VALUES (?,?)");

			ps1.setInt(1, lastId);
			
			ps1.setInt(2, parentCommentId);
			
			ps1.executeUpdate();
			rs.close();
			
			PreparedStatement ps2=session.prepareStatement(UPDATE_USER_COMMENT_COUNTER);
			
			ps2.setString(1,login);

			ps2.executeUpdate();
			
			PreparedStatement ps3=session.prepareStatement(SELECT_GIVEN_COMMENT_TOPIC);
			
			ps3.setInt(1,parentCommentId);
			
			ResultSet rs1=ps3.executeQuery();
			
			while(rs1.next())
			{
				PreparedStatement ps4=session.prepareStatement("INSERT INTO  comment_topic (TOPIC_ID ,COMMENT_ID)VALUES (?,?)");
				
				ps4.setInt(1, rs1.getInt("TOPIC_ID"));
				
				ps4.setInt(2, lastId);
				
				ps4.executeUpdate();
				
				ps4.close();
			}
			rs1.close();
			
			session.commit();
			
			disconnect();
			
		}
		catch(Exception e)
		{
			try
			{
				session.rollback();
			}
			catch(SQLException e1)
			{
				throw new ServerDownException(e1.getLocalizedMessage());
			}
		}
	}
	
	
	/**
	 * Method that inserts a comment into the Database.
	 * @param login
	 * @param bankId
	 * @param topic
	 * @param fecha
	 * @param time
	 * @throws ServerDownException 
	 */
	public void insertComment(String login, int bankId, Date fecha, Time time, String comment, ArrayList<Topic> topics, String heading) throws ServerDownException
	{

		try
		{
			connect();
			
			PreparedStatement p=session.prepareStatement(START_TRANSACTION);
			p.execute();
			
			PreparedStatement ps=session.prepareStatement("INSERT INTO comments( DATE, TIME, COMMENT, LOGIN, BANK_ID,TYPE,HEADING,HELPFUL, INAPPROPIATE) VALUES (?,?,?,?,?,?,?,?,?)");
			
			ps.setDate(1,fecha);
			
			ps.setTime(2, time);
			
			ps.setString(3, comment);
			
			ps.setString(4,login);
			
			ps.setInt(5,bankId);
			
			ps.setInt(6, DEFAULT_TYPE);

			
			ps.setString(7, heading);
			
			ps.setInt(8, DEFAULT_HELPFUL);
			
			ps.setString(9, DEFAULT_INAPPROPIATE);
			
			ps.executeUpdate();
			
			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery(SELECT_LAST_COMMENT_ID);

			rs.next();
			
			int lastId=rs.getInt("COMMENT_ID");
			
			for(int i=0;i<topics.size();i++)
			{
				PreparedStatement ps1=session.prepareStatement("INSERT INTO  comment_topic (TOPIC_ID ,COMMENT_ID)VALUES (?,?)");

				int topicId=topics.get(i).getTopic_id();
				
				ps1.setInt(1, topicId);
				
				ps1.setInt(2, lastId);

				ps1.executeUpdate();
				ps1.close();
			}
			
			PreparedStatement ps2=session.prepareStatement(UPDATE_USER_COMMENT_COUNTER);
			
			ps2.setString(1,login);
			
			ps2.executeUpdate();
			
			session.commit();
			
			disconnect();
			
		}
		catch(Exception e)
		{
			try
			{

				session.rollback();
			}
			catch(SQLException e1)
			{
				throw new ServerDownException(e.getLocalizedMessage());
			}
			
		}
		
	}
	
	

	/**
	 * Method that authenticates the user
	 * @param login
	 * @param password
	 * @return Returns true if the authentication is correct. Otherwise it returns false
	 * @throws ServerDownException 
	 */
	public User login(String login, String password) throws  ServerDownException
	{
		try
		{
			connect();
			
			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery("SELECT PASSWORD, ALIAS, FIRST_NAME, LAST_NAME, PHOTO, RANKING, POINTS from users where LOGIN="+APOSTROPHE + login+ APOSTROPHE);
			
			User user = null;
			
			//Verifies that the field password has text
			if(rs.next())
			{
				String retrievedPassword=rs.getString("PASSWORD");
				
				String decriptedPassword=Security.decrypt(retrievedPassword, Base64.decodeBase64(KEY));
				
				//Verifies that the given password matches the password on the database
				if(decriptedPassword.equalsIgnoreCase(password))
				{
					user = new User(login, rs.getString("FIRST_NAME"), rs.getString("LAST_NAME"), rs.getString("ALIAS"), rs.getString("PHOTO"));
				}
			}
			
			disconnect();
			return user;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	

	/**
	 * Method that verifies the login availability.
	 * @param login
	 * @return Returns true if the login given by the user is available. Otherwise returns false.
	 * @throws ServerDownException 
	 */
	public boolean verifyLogAv(String login) throws ServerDownException
	{
		try
		{
			connect();
			
			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery("SELECT LOGIN FROM users WHERE LOGIN="+APOSTROPHE +login+ APOSTROPHE);
			
			boolean ret = !rs.next();
			
			disconnect();
			
			return ret;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	/**
	 * Method that verifies the alias availability.
	 * @param login
	 * @return Returns true if the login given by the user is available. Otherwise returns false.
	 * @throws ServerDownException 
	 */
	public boolean verifyAliasAv(String alias) throws ServerDownException
	{
		try
		{
			connect();
			
			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery("SELECT ALIAS FROM users WHERE ALIAS="+APOSTROPHE +alias+ APOSTROPHE);
			
			boolean ret = !rs.next();
			
			disconnect();
			
			return ret;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	
	/**
	 * Return the 5 most recent comments.
	 */
	public ArrayList<Comment> getRecentComments() throws ServerDownException
	{
		try
		{
			//TODO
			ArrayList<Comment> recentComments= new ArrayList<Comment>();
			connect();
			Statement st = session.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = st.executeQuery(SELECT_5_RECENT_COMMENTS);
			
			int commentId;
			
			while(rs.next())
			{
				commentId=rs.getInt("COMMENT_ID");

				ArrayList<String> topics=getTopicsGivenCommentID(commentId);
				
				

				ArrayList<RelatedComment> relatedComments=getRelatedCommentsGivenCommentId(commentId);
				

				
				Comment comment1=new Comment(rs.getInt("COMMENT_ID"),rs.getString("LOGIN"),rs.getString("COMMENT"),rs.getInt("BANK_ID"),rs.getDate("DATE"),rs.getTime("TIME"), topics, relatedComments,rs.getString("HEADING"), rs.getInt("HELPFUL"),rs.getString("INAPPROPIATE"));
				
				recentComments.add(comment1);
			}
			rs.close();
			disconnect();
			return recentComments;
			
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
		
	}
	
	/**
	 * This method returns a String arraylist with the topics associated to a comment.
	 * @return
	 * @throws ServerDownException 
	 * @throws SQLException 
	 */
	public ArrayList<String> getTopicsGivenCommentID(int commentId) throws ServerDownException
	{
		try 
		{
			ArrayList<String> topics = new ArrayList<String>();
			
			PreparedStatement pt=session.prepareStatement(SELECT_TOPIC_GIVEN_COMMENT_ID);
			
			pt.setInt(1, commentId);
			
			ResultSet rs= pt.executeQuery();
			
			while(rs.next())
			{
				String topic=rs.getString("TOPIC");

				topics.add(topic);
			}
			rs.close();
			
			return topics;

		} 
		catch (Exception e) 
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	public ArrayList<RelatedComment> getRelatedCommentsGivenCommentId(int commentId) throws ServerDownException
	{
		try
		{
			ArrayList<RelatedComment> relatedComments= new ArrayList<RelatedComment>();
			
			PreparedStatement pt1=session.prepareStatement(SELECT_RELATED_COMMENTS_GIVEN_COMMENT_ID);
			
			pt1.setInt(1, commentId);
			
			ResultSet rs=pt1.executeQuery();
			
			while(rs.next())
			{
				
				RelatedComment relatedComment=new RelatedComment(rs.getInt("COMMENT_ID"), rs.getString("COMMENT"),rs.getDate("DATE"),rs.getTime("TIME"),rs.getString("LOGIN"),rs.getString("INAPPROPIATE"), commentId,rs.getInt("HELPFUL"));
				relatedComments.add(relatedComment);
				
			}
			rs.close();
			return relatedComments;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	/**
	 * Get the user's info associated with the given login.
	 */
	public User getUser(String logIn) throws ServerDownException
	{
		try
		{
			connect();			
			User u =userQueries().getUserInfo(logIn, session);
			return u;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	/**
	 * Get the bank's name, matching the given id.
	 */
	public String getBank(String bankId) throws ServerDownException
	{
		try
		{
			connect();
			
			PreparedStatement st = session.prepareStatement(SELECT_BANK_NAME_GIVEN_BANK_ID);
			
			st.setInt(1,Integer.parseInt(bankId));
			
			ResultSet rs=st.executeQuery();
			
			String u = null;
			
			if(rs.next())
				u = rs.getString("NAME");
			
			disconnect();
			
			return u;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	/**
	 * Returns true if a comment has been marked as helpful by the user with the given login. Otherwise returns false
	 * @throws ServerDownException 
	 */
	public boolean getLoginHelpful(String login, int commentId) throws ServerDownException
	{
		try
		{
			boolean answer;
			
			connect();
			
			PreparedStatement st= session.prepareStatement(SELECT_HELPFUL_COMMENTS_USER);
			
			st.setInt(1, commentId);
			
			st.setString(2, login);
			
			ResultSet rs=st.executeQuery();
			rs.next();
			if(rs.getInt("CONTADOR")>0)
				answer=true;
			else
				answer=false;
			
			rs.close();
			
			disconnect();
			return answer;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
		
	}
	
	public boolean getLoginInappropiate(int commentId) throws ServerDownException
	{
		try
		{
			boolean answer = true;
			
			connect();
			
			PreparedStatement ps=session.prepareStatement(SELECT_INAPPROPIATE_COMMENTS_USER);
			
			ps.setInt(1,commentId);
			
			ResultSet rs=ps.executeQuery();
			
			rs.next();
			
			if(rs.getString("INAPPROPIATE").equals(""))
				answer=false;
			
			rs.close();
			disconnect();
			return answer;
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	/**
	 * Method that marks a comment as helpful. 
	 */
	public void markCommentAsHelpful(int commentId, String login) throws ServerDownException
	{
		try
		{
			
			connect();
			PreparedStatement p1=session.prepareStatement(START_TRANSACTION);
			p1.execute();
			
			userQueries().updateUserHelpfulCounter(login, session);
			
			PreparedStatement ps1=session.prepareStatement(UPDATE_COMMENT_HELPFUL_COUNTER);
			ps1.setInt(1,commentId);
			ps1.executeUpdate();
			
			PreparedStatement ps2=session.prepareStatement(UPDATE_TABLE_HELPFUL);
			ps2.setString(1,login);
			ps2.setInt(2,commentId);
			ps2.execute();
			
			session.commit();
			
			disconnect();
		}
		catch(Exception e)
		{
			try
			{
				session.rollback();
			}
			catch(SQLException e1)
			{
				throw new ServerDownException(e1.getLocalizedMessage());
			}
		}
	}
	
	/**
	 * Method that marks a comment as inappropiate.
	 */
	public void markCommentAsInappropiate(int commentId, String login) throws ServerDownException
	{
		//agregar boolean para saber si es related comment o comment normal
		//saved comments
		//dadas 3 letras buscar el nombre de un banco o topics
		//poner contador de usado a los bancos y a los topics
		//hacer eso en el insert.
		//hacer tabla related_comment helpful
		//voler a hacer tabla related-comment como estaba.
		//insertar related comment 
		//get recent comments
		//modificar getCommentInfo(int commentId)
		
		try
		{
			connect();
			
			PreparedStatement ps=session.prepareStatement("UPDATE comments SET INAPPROPIATE=? WHERE COMMENT_ID=? ");
			ps.setString(1,login);
			ps.setInt(2, commentId);
			ps.executeUpdate();
			disconnect();
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	/**
	 * Method that gets a comment information.Only works for parent comments! 
	 */
	public Comment getCommentInfo(int commentId) throws ServerDownException
	{
		try
		{
			connect();
			
			PreparedStatement ps=session.prepareStatement(SELECT_COMMENT_GIVEN_COMMENT_ID);
			ps.setInt(1, commentId);
			
			ResultSet rs=ps.executeQuery();
			
			ArrayList<String> topics=getTopicsGivenCommentID(commentId);
			
			ArrayList<RelatedComment> relatedComments=getRelatedCommentsGivenCommentId(commentId);
			
			Comment comment = null;
			
			if(rs.next())
				comment= new Comment(rs.getInt("COMMENT_ID"),rs.getString("LOGIN"),rs.getString("COMMENT"),rs.getInt("BANK_ID"),rs.getDate("DATE") ,rs.getTime("TIME"),topics,relatedComments, rs.getString("HEADING"),rs.getInt("HELPFUL"),rs.getString("INAPPROPIATE"));
			
			rs.close();
			disconnect();
			return comment;
			
		}
		catch(Exception e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	public void saveComment(String login, int commentId) throws ServerDownException
	{
		try 
		{
			connect();
			savedComments().saveComment(login, commentId, session);
			disconnect();
		} 
		catch (ServerDownException e) 
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
	public boolean getLoginSavedComment(String login, int commentId) throws ServerDownException
	{
		try
		{
			connect();
			return savedComments().getLoginSavedComment(login, commentId, session);
		}
		catch(ServerDownException e)
		{
			throw new ServerDownException(e.getLocalizedMessage());
		}
	}
	
}
